﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace DataAccess {
    public class PartnershipRepository : DataProvider {
        public DataTable getPartnerships(int partnerID) {
            String sql = string.Format("SELECT Partnerships.BeginDate, Partnerships.EndDate, Products.ProductID, Products.ProductName, Products.ProductPrice, Products.ProductPhotoURL " +
                                       "FROM Products INNER JOIN Partnerships ON Products.ProductID = Partnerships.ProductID " +
                                       "WHERE (Partnerships.PartnerID = {0})", partnerID);
            return this.executeQueryDataTable(sql);
        }

        /// <summary>
        /// add new partnerships, return id
        /// </summary>
        /// <param name="partnerID"></param>
        /// <param name="productID"></param>
        /// <param name="beginDate"></param>
        /// <param name="endDate"></param>
        /// <param name="minQuanlity"></param>
        /// <param name="nextTransQuanlity"></param>
        /// <param name="state"></param>
        /// <returns></returns>
        public int add(int partnerID, int productID, DateTime beginDate, DateTime endDate, int minQuanlity, int nextTransQuanlity, int state = 0) {
            String sql = "INSERT INTO Partnerships(PartnerID, ProductID, BeginDate, EndDate, MinQuanlity, NextTransQuanlity, State) VALUES({0}, {1}, '{2}', '{3}', {4}, {5}, {6})";
            sql = string.Format(sql, partnerID, productID, beginDate, endDate, minQuanlity, nextTransQuanlity, state);
            if (this.executeQuery(sql) == 1) {
                sql = "SELECT MAX(ID) FROM Partnerships";
                object obj = this.executeScalar(sql);
                try {
                    int result = int.Parse(obj.ToString());
                    return result;
                }
                catch (Exception ex) {
                    return -1;
                }
            }

            return -1; //fail
        }
    }
}
